﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Golos.Helper;
using Golos.Users.Configuration;

namespace Golos.Users.UserCache
{
    public partial class Cache
    {

        private void GetUserCacheFromDb()
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("select ID, Name, email from Users", connection);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                            return;
                        while (reader.Read())
                        {
                            Guid userId = reader.GetGuid(0);
                            string name = reader.GetString(1);
                            string email = reader.GetString(2);
                            users.Add(userId, new UserInfo(userId, name, email));
                        }
                    }
                    foreach (Guid userId in users.Keys)
                    {
                        command = new SqlCommand(string.Format("select [UserIdType] ,[IdentificationId],[SaveActivityToSocialWeb] from [UserProperties] where [UserID]='{0}'", userId), connection);
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (!reader.HasRows)
                                continue;
                            while (reader.Read())
                            {
                                UserIdType idType = (UserIdType) reader.GetInt32(0);
                                string identificationId = reader.GetString(1);
                                bool saveActivityToSocialWeb = reader.GetBoolean(2);
                                users[userId].AttachUserIdentification(idType, identificationId, saveActivityToSocialWeb);
                            }
                            
                        }
                    };
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    throw;
                }

            }
        }

        private static bool FlushNewUserToDb(Guid newUserId, string name, string email, UserIdType userIdType, string identificationId, bool saveActivityToSocialWeb)
        {
            /*
             INSERT INTO [Users] ([ID] ,[Name] ,[email]) VALUES (<ID, uniqueidentifier,> ,<Name, nvarchar(50),> ,<email, nvarchar(255),>)
             INSERT INTO [UserProperties] ([UserID] ,[vkId] ,[fbId] ,[twId]) VALUES (<UserID, uniqueidentifier,> ,<vkId, nvarchar(50),> ,<fbId, nvarchar(50),> ,<twId, nvarchar(50),>)
             INSERT INTO [UserSettings] ([UserId],[ParamName],[Value]) VALUES (<UserId, uniqueidentifier,>,,<ParamName, nvarchar(255),>,,<Value, nvarchar(255),>)
            */
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand(string.Format("INSERT INTO [Users] ([ID] ,[Name] ,[email]) VALUES ('{0}' ,'{1}' ,'{2}')", newUserId, name, email), connection, transaction);
                    command.ExecuteNonQuery();
                    command = new SqlCommand("INSERT INTO [UserProperties] ([UserID] ,[UserIdType] ,[IdentificationId],[SaveActivityToSocialWeb]) VALUES (@userId ,@userIdType ,@identificationId, @saveActivityToSocialWeb)", connection, transaction);
                    command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
                    command.Parameters.Add("@userIdType", SqlDbType.Int);
                    command.Parameters.Add("@identificationId", SqlDbType.VarChar);
                    command.Parameters.Add("@saveActivityToSocialWeb", SqlDbType.Bit);
                    command.Parameters["@userId"].Value = newUserId;
                    command.Parameters["@userIdType"].Value = (int)userIdType;
                    command.Parameters["@identificationId"].Value = identificationId;
                    command.Parameters["@saveActivityToSocialWeb"].Value = saveActivityToSocialWeb;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }

            }
        }

        private void SaveUserIdentificationToDb(Guid userId, UserIdType userIdType, string identificationId, bool saveActivityToSocialWeb, SqlConnection connection, SqlTransaction transaction)
        {
            SqlCommand command = new SqlCommand("UPDATE [UserProperties] SET [IdentificationId] = @identificationId, [SaveActivityToSocialWeb] = @saveActivityToSocialWeb WHERE [UserID] = @userId AND [UserIdType]=@userIdType", connection, transaction);
            command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
            command.Parameters.Add("@userIdType", SqlDbType.Int);
            command.Parameters.Add("@identificationId", SqlDbType.VarChar);
            command.Parameters.Add("@saveActivityToSocialWeb", SqlDbType.Bit);
            command.Parameters["@userId"].Value = userId;
            command.Parameters["@userIdType"].Value = (int)userIdType;
            command.Parameters["@identificationId"].Value = identificationId;
            command.Parameters["@saveActivityToSocialWeb"].Value = saveActivityToSocialWeb;
            if (command.ExecuteNonQuery() == 1)
            {
                return;
            }
            command = new SqlCommand("INSERT INTO [UserProperties] ([UserID] ,[UserIdType] ,[IdentificationId],[SaveActivityToSocialWeb]) VALUES (@userId ,@userIdType ,@identificationId, @saveActivityToSocialWeb)", connection, transaction);
            command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
            command.Parameters.Add("@userIdType", SqlDbType.Int);
            command.Parameters.Add("@identificationId", SqlDbType.VarChar);
            command.Parameters.Add("@saveActivityToSocialWeb", SqlDbType.Bit);
            command.Parameters["@userId"].Value = userId;
            command.Parameters["@userIdType"].Value = (int)userIdType;
            command.Parameters["@identificationId"].Value = identificationId;
            command.Parameters["@saveActivityToSocialWeb"].Value = saveActivityToSocialWeb;
            command.ExecuteNonQuery();
        }

        private bool SaveUserIdentificationToDb(Guid userId, UserIdType userIdType, string identificationId, bool saveActivityToSocialWeb)
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SaveUserIdentificationToDb(userId, userIdType, identificationId, saveActivityToSocialWeb, connection, transaction);
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }

            }
        }

        private bool SaveUserIdentificationToDb(Guid userId, UserIdType userIdType, string identificationId, bool saveActivityToSocialWeb, List<Guid> foundUsers)
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    foreach (Guid foundUser in foundUsers)
                    {
                        SqlCommand command = new SqlCommand("DELETE FROM [USERS] WHERE [UserID] = @userId", connection, transaction);
                        command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
                        command.Parameters["userId"].Value = foundUser;
                        command.ExecuteNonQuery();
                    }
                    SaveUserIdentificationToDb(userId, userIdType, identificationId, saveActivityToSocialWeb, connection, transaction);
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }

            }
        }

        private bool UpdateProfileInDb(UserInfo userInfo)
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand("UPDATE [Users] SET [Name]=@name, [email]=@email WHERE [ID]=@userId", connection, transaction);
                    command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
                    command.Parameters.Add("@name", SqlDbType.VarChar);
                    command.Parameters.Add("@email", SqlDbType.VarChar);
                    command.Parameters["@userId"].Value = userInfo.Id;
                    command.Parameters["@name"].Value = userInfo.Name;
                    command.Parameters["@email"].Value = userInfo.Email;
                    if(command.ExecuteNonQuery()!=1)
                        throw new Exception("Can't update in DB. User not found");
                    foreach (UserIdType userIdType in userInfo.UserIdentification.Keys)
                    {
                        SaveUserIdentificationToDb(userInfo.Id, userIdType, userInfo.UserIdentification[userIdType], userInfo.SaveActivityToSocialWeb[userIdType], connection, transaction);
                    }
                    transaction.Commit();
                    return true;
                    
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }

            }
        }

        
    }
}
